﻿using System;
using System.Linq;
using System.Data.SQLite;
using SharpStockLib.Data;
using System.Data;
using System.IO;
using SharpStockLib.Fin;

namespace SharpStockLib.DataAccess.Persisters
{
    /// <summary>
    /// Helps building or updating the quotes database. Provides methods for creating a schema, inserting new instruments, inserting new quotes.
    /// </summary>
    public class SQLitePersister : IDisposable
    {
        private readonly SQLiteConnection _connection;

        /// <summary>
        /// Default constructor.
        /// WARNING : make sure the instance is disposed (via .Dispose() or in a using() block) when not used anymore, or existing connection will not be closed.
        /// </summary>
        /// <param name="pathToDB">Path to the SQLite DB file.</param>
        public SQLitePersister(string pathToDB)
        {
            _connection = new SQLiteConnection("Data Source=" + pathToDB + ";Version=3;Foreign Keys=True;Synchronous=Off;Journal Mode=WAL;");
            _connection.Open();
        }

        /// <summary>
        /// "Brute force" reader and persister : will insert each line as soon as it is read inside a BEGIN/COMMIT block. Good performance, terrible code (parsing-wise).
        /// This was created mostly for the original small code snippet that would download the entire NYSE quotes from Yahoo :p
        /// </summary>
        /// <param name="pathToCSV">Path to the CSV file to read</param>
        /// <param name="instrumentName">Name of the stock the data will be associated to.</param>
        public void Persist(string pathToCSV, string instrumentName)
        {
            if (_connection != null && _connection.State == ConnectionState.Open)
            {
                InsertStockName(instrumentName);

                SQLiteTransaction transac = _connection.BeginTransaction();

                using (StreamReader csvReader = new StreamReader(pathToCSV))
                {
                    // Skips the first line
                    csvReader.ReadLine();

                    using (SQLiteCommand cmd = _connection.CreateCommand())
                    {
                        cmd.CommandText = "INSERT INTO eod(symbol, date, open, high, low, close, volume) VALUES (?, ?, ?, ?, ?, ?, ?)";
                        SQLiteParameter param1 = cmd.CreateParameter();
                        cmd.Parameters.Add(param1);
                        param1.Value = instrumentName.ToUpper();
                        SQLiteParameter paramDate = cmd.CreateParameter();
                        cmd.Parameters.Add(paramDate);
                        SQLiteParameter paramOpen = cmd.CreateParameter();
                        cmd.Parameters.Add(paramOpen);
                        SQLiteParameter paramHigh = cmd.CreateParameter();
                        cmd.Parameters.Add(paramHigh);
                        SQLiteParameter paramLow = cmd.CreateParameter();
                        cmd.Parameters.Add(paramLow);
                        SQLiteParameter paramClose = cmd.CreateParameter();
                        cmd.Parameters.Add(paramClose);
                        SQLiteParameter paramVol = cmd.CreateParameter();
                        cmd.Parameters.Add(paramVol);

                        while (!csvReader.EndOfStream)
                        {
                            // Parses the line
                            string line = csvReader.ReadLine();
                            if (line == null)
                                continue;
                            string[] elements = line.Split(',');

                            if (elements.Count() >= 6)
                            {
                                // Inserts the data
                                paramDate.Value = elements[0];
                                paramOpen.Value = double.Parse(elements[1]);
                                paramHigh.Value = double.Parse(elements[2]);
                                paramLow.Value = double.Parse(elements[3]);
                                paramClose.Value = double.Parse(elements[4]);
                                paramVol.Value = int.Parse(elements[5]);
                                cmd.ExecuteNonQuery();
                            }
                        }
                    }
                }

                transac.Commit();
                transac.Dispose();
            }
        }

        /// <summary>
        /// Rx helper for asynchronous compatibility.
        /// </summary>
        /// <param name="stockData"></param>
        /// <param name="instrumentName"></param>
        public void Persist(IObservable<IData> stockData, string instrumentName)
        {
            if (_connection != null && _connection.State == ConnectionState.Open)
            {
                InsertStockName(instrumentName);

                SQLiteTransaction transac = _connection.BeginTransaction();

                using (stockData.Subscribe(data => PersistLine(data, instrumentName),
                    () =>
                    {
                        transac.Commit();
                        transac.Dispose();
                    }))
                {
                }
            }
        }

        /// <summary>
        /// Basic method to insert a single IData into the DB.
        /// </summary>
        /// <param name="data"></param>
        /// <param name="symbol">Name of the symbol. If it starts with a '/' (slash) character, the data will be considered a futures quote. Otherwise, it will be considered a stock quote.</param>
        public void PersistLine(IData data, string symbol)
        {
            if (data == null)
                throw new ArgumentNullException("data", "Cannot insert null data in DB !");

            if(string.IsNullOrEmpty(symbol))
                throw new ArgumentNullException("symbol", "Need the instrument's name in order to insert data !");

            string tableName = symbol.StartsWith("/") ? "eodFut" : "eod";

            // Actual insert into the DB.
            if (_connection != null && _connection.State == ConnectionState.Open)
            {
                using (SQLiteCommand cmd = _connection.CreateCommand())
                {
                    cmd.CommandText = "INSERT INTO " + tableName + "(symbol, date, open, high, low, close, volume) VALUES (?, ?, ?, ?, ?, ?, ?)";
                    SQLiteParameter param1 = cmd.CreateParameter();
                    cmd.Parameters.Add(param1);
                    param1.Value = symbol.ToUpper();
                    SQLiteParameter paramDate = cmd.CreateParameter();
                    cmd.Parameters.Add(paramDate);
                    SQLiteParameter paramOpen = cmd.CreateParameter();
                    cmd.Parameters.Add(paramOpen);
                    SQLiteParameter paramHigh = cmd.CreateParameter();
                    cmd.Parameters.Add(paramHigh);
                    SQLiteParameter paramLow = cmd.CreateParameter();
                    cmd.Parameters.Add(paramLow);
                    SQLiteParameter paramClose = cmd.CreateParameter();
                    cmd.Parameters.Add(paramClose);
                    SQLiteParameter paramVol = cmd.CreateParameter();
                    cmd.Parameters.Add(paramVol);

                    paramDate.Value = data.Date.ToString("yyyy-MM-dd");
                    paramOpen.Value = data.Open;
                    paramHigh.Value = data.High;
                    paramLow.Value = data.Low;
                    paramClose.Value = data.Close;
                    paramVol.Value = data.Volume;

                    cmd.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// Adds a stock description (symbol, actual name and sector) into the stocks table.
        /// </summary>
        /// <param name="stock">The stock to persist.</param>
        public void PersistStock(Stock stock)
        {
            if (_connection != null && _connection.State == ConnectionState.Open)
            {
                bool stockExists = IsStockInDB(stock.Symbol);

                // Stock does not exist in DB : inserts it
                if (!stockExists)
                {
                    using (SQLiteCommand cmd = _connection.CreateCommand())
                    {
                        cmd.CommandText = "INSERT INTO stocks(symbol,name,sector) VALUES (?,?,?)";
                        SQLiteParameter param2 = cmd.CreateParameter();
                        cmd.Parameters.Add(param2);
                        param2.Value = stock.Symbol.ToUpper();

                        SQLiteParameter param3 = cmd.CreateParameter();
                        cmd.Parameters.Add(param3);
                        param3.Value = stock.Description;

                        SQLiteParameter param4 = cmd.CreateParameter();
                        cmd.Parameters.Add(param4);
                        param4.Value = stock.Sector;

                        cmd.ExecuteNonQuery();
                    }
                }
            }
        }

        /// <summary>
        /// Helper. Inserts only a stock's ticker into the stocks table (no actual name or sector).
        /// </summary>
        /// <param name="stockName">The symbol of the stock (e.g. "AAPL")</param>
        public void InsertStockName(string stockName)
        {
            InsertSymbol(stockName, "stocks");
        }

        /// <summary>
        /// Helper. Inserts only a futures's ticker into the stocks table (no actual name or sector).
        /// </summary>
        /// <param name="futName">The symbol of the stock (e.g. "/ES")</param>
        public void InsertFuturesName(string futName)
        {
            InsertSymbol(futName, "futures");
        }

        /// <summary>
        /// Inserts a given symbol into the target table. SQLite qill ignore the data if it already exists.
        /// </summary>
        /// <param name="symbol">The symbol to insert.</param>
        /// <param name="tableName">The table to insert the symbol into.</param>
        private void InsertSymbol(string symbol, string tableName)
        {
            if (_connection != null && _connection.State == ConnectionState.Open)
            {
                using (SQLiteCommand cmd = _connection.CreateCommand())
                {
                    cmd.CommandText = "INSERT OR IGNORE INTO " + tableName + " (symbol) VALUES (?)";
                    SQLiteParameter param2 = cmd.CreateParameter();
                    cmd.Parameters.Add(param2);
                    param2.Value = symbol.ToUpper();
                    cmd.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// Checks if the given symbol exists in the stocks table.
        /// </summary>
        /// <param name="stockName">The symbol to check.</param>
        /// <returns>True if the symbol exists in the DB, false otherwise.</returns>
        private bool IsStockInDB(string stockName)
        {
            SQLiteCommand cmd = _connection.CreateCommand();

            cmd.CommandText = "SELECT 1 FROM stocks WHERE symbol=?";
            SQLiteParameter param = cmd.CreateParameter();
            cmd.Parameters.Add(param);
            param.Value = stockName.ToUpper();
            bool stockExists = cmd.ExecuteScalar() != null;
            cmd.Dispose();

            return stockExists;
        }

        /// <summary>
        /// Checks if the given symbol has quotes in the eod or eodFut table.
        /// </summary>
        /// <param name="symbol">The symbol to check. If it starts with a '/' (slash) character, then it will be considered a future instrument. Otherwise, it will be considered a stock.</param>
        /// <returns>True if the symbol has quotes in the DB, false otherwise.</returns>
        public bool AreQuotesInDB(string symbol)
        {
            SQLiteCommand cmd = _connection.CreateCommand();

            string tableName = symbol.StartsWith("/") ? "eodFut" : "eod";

            cmd.CommandText = "SELECT 1 FROM " + tableName + " WHERE symbol=?";
            SQLiteParameter param = cmd.CreateParameter();
            cmd.Parameters.Add(param);
            param.Value = symbol.ToUpper();
            bool stockExists = cmd.ExecuteScalar() != null;
            cmd.Dispose();

            return stockExists;
        }


        /// <summary>
        /// Small helper to check the database is fine and all tables exist.
        /// </summary>
        /// <returns></returns>
        public bool IsDatabaseValid()
        {
            if (_connection != null && _connection.State == ConnectionState.Open)
            {
                DataTable schema = _connection.GetSchema("Tables");
                if(schema != null)
                {
                    DataRow[] tables = schema.Select("Table_Name = 'stocks' OR Table_Name = 'eod' OR Table_Name = 'futures' OR Table_Name = 'eodFut'");
                    return tables.Length == 4;
                }
            }

            return false;
        }

        /// <summary>
        /// Creates the database from scratch by creating the tables.
        /// </summary>
        public void CreateSchema()
        {
            if (_connection != null && _connection.State == ConnectionState.Open)
            {
                // Creates the Stocks table
                SQLiteCommand cmd = _connection.CreateCommand();
                cmd.CommandText = "CREATE TABLE IF NOT EXISTS stocks (symbol VARCHAR(16) PRIMARY KEY, name VARCHAR(255), sector VARCHAR(32), isETF BOOLEAN);";
                cmd.ExecuteNonQuery();

                // Creates the Futures table
                cmd.CommandText = "CREATE TABLE IF NOT EXISTS futures (symbol VARCHAR(16) PRIMARY KEY, tickValue DOUBLE, increment DOUBLE, expi DATE, margin DOUBLE);";
                cmd.ExecuteNonQuery();

                // Creates the EOD table for stocks
                cmd.CommandText = "CREATE TABLE IF NOT EXISTS eod (symbol VARCHAR(16), date DATE, open DOUBLE, high DOUBLE, low DOUBLE, close DOUBLE, volume INTEGER, CONSTRAINT pk PRIMARY KEY (symbol, date) ON CONFLICT FAIL, FOREIGN KEY (symbol) REFERENCES stocks (symbol));";
                cmd.ExecuteNonQuery();

                // Creates the EOD for futures table ; point is to keep the foreign key constraint here
                cmd.CommandText = "CREATE TABLE IF NOT EXISTS eodFut (symbol VARCHAR(16), date DATE, open DOUBLE, high DOUBLE, low DOUBLE, close DOUBLE, volume INTEGER, CONSTRAINT pk PRIMARY KEY (symbol, date) ON CONFLICT FAIL, FOREIGN KEY (symbol) REFERENCES futures (symbol));";
                cmd.ExecuteNonQuery();

                // Index on the date column (stocks)
                cmd.CommandText = "CREATE INDEX IF NOT EXISTS dateIdx on eod(date);";
                cmd.ExecuteNonQuery();

                // Index on the date column (futures)
                cmd.CommandText = "CREATE INDEX IF NOT EXISTS dateIdx on eodFut(date);";
                cmd.ExecuteNonQuery();

                cmd.Dispose();
            }
        }


        #region IDisposable Members

        /// <summary>
        /// Closes and disposes the underlying SQLite connection.
        /// </summary>
        public void Dispose()
        {
            if (_connection != null && _connection.State != ConnectionState.Closed)
            {
                // Cleans up the DB a bit
                SQLiteCommand cmd = _connection.CreateCommand();
                cmd.CommandText = "VACUUM;";
                cmd.ExecuteNonQuery();
                cmd.Dispose();

                // Closes the connection
                _connection.Close();
                _connection.Dispose();
            }
        }

        #endregion
    }
}
